package com.oasystem.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import com.oasystem.domain.Goods;
import com.oasystem.domain.PagingBean;

public class GoodsDAO {
	private JdbcTemplate jdbcTemplate;

	public void insertGoods(final Goods goods) {
		
		
		jdbcTemplate.update(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection
						.prepareStatement("insert into goods (name,price) values(?,?)");
				ps.setString(1, goods.getName());
				ps.setDouble(2, goods.getPrice());
				return ps;
			}
		});
	}
	
	public boolean isGoodsNameNotExist(String name){
		return 0==jdbcTemplate.queryForInt("select count(id) from goods where name=? ", new Object[]{name});
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Goods> getPagingGoods(PagingBean<Goods> pagingBean) {

		final int currentRecordIndex = pagingBean.getCurrentRecordIndex();
		final int pageSize = pagingBean.getPageSize();
		pagingBean.setAllRecordCount(jdbcTemplate
				.queryForInt("select count(id) from goods"));

		List<Goods> list = jdbcTemplate.query(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con
						.prepareStatement("select id,name,price from goods order by id desc limit ?,?");
				ps.setInt(1, currentRecordIndex);
				ps.setInt(2, pageSize);
				return ps;
			}
		}, new RowMapper<Goods>() {

			public Goods mapRow(ResultSet rs, int rowNum) throws SQLException {
				Goods goods = new Goods();
				goods.setId(rs.getInt("id"));
				goods.setName(rs.getString("name"));
				goods.setPrice(rs.getDouble("price"));
				return goods;
			}
		});
		pagingBean.setData(list);
		return list;
	}

	public void load(Goods goods) {
		Goods result = jdbcTemplate.queryForObject(
				"select * from goods where id=?",
				new Object[] { goods.getId() }, new RowMapper<Goods>() {

					public Goods mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Goods goods = new Goods();
						goods.setId(rs.getInt("id"));
						goods.setName(rs.getString("name"));
						goods.setPrice(rs.getDouble("price"));
						return goods;
					}
				});
		goods.copy(result);
	}

	public void updateGoods(final Goods goods) {
		jdbcTemplate.update("update goods set name=?,price=? where id=?",
				new PreparedStatementSetter() {

					public void setValues(PreparedStatement ps)
							throws SQLException {
						ps.setString(1, goods.getName());
						ps.setDouble(2, goods.getPrice());
						ps.setInt(3, goods.getId());
					}
				});

	}

	public void deleteGoods(Goods goods) {
		jdbcTemplate.update("delete from goods where id=?",
				new Object[] { goods.getId() });
	}

	public List<Goods> getAllGoods() {
		return jdbcTemplate.query(
				"select id,name,price from goods order by id desc",
				new RowMapper<Goods>() {
					public Goods mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Goods goods = new Goods();
						goods.setId(rs.getInt("id"));
						goods.setName(rs.getString("name"));
						goods.setPrice(rs.getDouble("price"));
						return goods;
					}
				});
	}
}
